package com.ph.irp.base.usermanage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.usermanage.dao.PBasePurviewUserDAO;
import com.ph.irp.model.PBasePurviewUser;
import com.ph.util.DBUtil;

public class PBasePurviewUserDAOImpl implements PBasePurviewUserDAO {

	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public boolean isExistedUser(String name)throws Throwable{
		boolean flag = false;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append(" select * from p_base_purview_user user where ");
			sb.append(" user.UserName='").append(name).append("'");
			
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			if (rs.next()) {
				flag = true;
			}
		} catch (Exception e) {			
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return flag;
	
		
	}

	public boolean isExistedUser(String name, String password)throws Throwable {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append(" select * from p_base_purview_user user where ");
			sb.append(" user.UserName='").append(name).append("'");
			sb.append(" and user.userPassword='").append(password).append("'");
			// con = DBConnection.getConnection();
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			if (rs.next()) {
				flag = true;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return flag;
	}

	@Override
	public List<PBasePurviewUser> getAllUser() throws Throwable{
		List<PBasePurviewUser> resList = new ArrayList<PBasePurviewUser>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.state stat,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username ");
			sb.append("from p_base_purview_user u");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewUser user = new PBasePurviewUser();

				user.setState(rs.getInt("stat"));
				user.setUserDesc(rs.getString("userdesc"));
				user.setUserId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));

				resList.add(user);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}
	
	public List<PBasePurviewUser> getAllUserExcludeCurrentUserID(int userID) throws Throwable{
		List<PBasePurviewUser> resList = new ArrayList<PBasePurviewUser>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.state stat,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username ");
			sb.append("from p_base_purview_user u ");
			sb.append("where UserID<>").append(userID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewUser user = new PBasePurviewUser();

				user.setState(rs.getInt("stat"));
				user.setUserDesc(rs.getString("userdesc"));
				user.setUserId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));

				resList.add(user);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List<Integer> getAllUserIds() throws Throwable{
		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.userid id ");
			sb.append("from p_base_purview_user u");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("id"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public PBasePurviewUser getUserByUserNameAndPassword(String name,
			String password) throws Throwable{
		PBasePurviewUser user = new PBasePurviewUser();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.state stat,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username, ");
			sb.append("u.CandiateID candiateID ");
			sb.append("from p_base_purview_user u ");
			sb.append("where username='").append(name).append("' ");
			sb.append("and u.userPassword='").append(password).append("'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				user.setState(rs.getInt("stat"));
				user.setUserDesc(rs.getString("userdesc"));
				user.setUserId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));
				user.setPBaseVerifyprojectCandiateId(rs.getInt("candiateID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return user;
	}

	public void addUser(PBasePurviewUser user)throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into p_base_purview_user(username,UserDesc,userPassword,CandiateID)");
			sb.append(" values('");
			sb.append(user.getUserName());
			sb.append("','").append(user.getUserDesc());
			sb.append("','").append(user.getPassword());
			sb.append("',").append(user.getPBaseVerifyprojectCandiateId());
			sb.append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public List<PBasePurviewUser> getUserListByUserName(String userName) throws Throwable{
		List<PBasePurviewUser> resList = new ArrayList<PBasePurviewUser>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.state stat,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username ");
			sb.append("from p_base_purview_user u");
			sb.append(" where username like '%").append(userName).append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewUser user = new PBasePurviewUser();

				user.setState(rs.getInt("stat"));
				user.setUserDesc(rs.getString("userdesc"));
				user.setUserId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));

				resList.add(user);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}
	
	public List<PBasePurviewUser> getUserListByUserNameExcludeCurrentUserID(
			String userName,int userID) throws Throwable{
		List<PBasePurviewUser> resList = new ArrayList<PBasePurviewUser>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.state stat,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username ");
			sb.append("from p_base_purview_user u ");
			sb.append("where username like '%").append(userName).append("%' ");
			sb.append("and UserID<>").append(userID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewUser user = new PBasePurviewUser();

				user.setState(rs.getInt("stat"));
				user.setUserDesc(rs.getString("userdesc"));
				user.setUserId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));

				resList.add(user);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	
		
	}

	public PBasePurviewUser getUserByUserId(String userId)throws Throwable {
		PBasePurviewUser result = new PBasePurviewUser();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.userPassword pas,u.userdesc userdesc,");
			sb.append("u.userid id,u.username username,u.CandiateID candiateID ");
			sb.append("from p_base_purview_user u ");
			sb.append("where UserID=").append(userId);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				result.setPassword(rs.getString("pas"));
				result.setUserDesc(rs.getString("userdesc"));
				result.setUserId(rs.getInt("id"));
				result.setUserName(rs.getString("username"));
				result.setPBaseVerifyprojectCandiateId(rs.getInt("candiateID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return result;
	}

	public void editUser(PBasePurviewUser user)throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update p_base_purview_user set UserName='");
			sb.append(user.getUserName()).append("',UserDesc='");
			sb.append(user.getUserDesc()).append("',userPassword='");
			sb.append(user.getPassword()).append("',CandiateID=");
			sb.append(user.getPBaseVerifyprojectCandiateId());
			
			
			sb.append(" where UserID=").append(user.getUserId());

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	// delete from lm_brand ");
	// sbf.append(" where id=").append(Integer.parseInt(brandID));
	//	
	public void deleteUser(String deleteIds)  throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_purview_user ");
			sb.append(" where UserID in (").append(deleteIds).append(")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
